﻿using System;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using ExcelAutomationRunner;
using System.IO;
using System.Collections.Generic;
using OfficeOpenXml;
using Microsoft.Office.Interop.Excel;

namespace UnitTestProject1
{
    [TestClass]
    public class UnitTest1
    {

        private readonly static object _lock = new object();

        public const string WORKSHEET_TO_WORKBOOK_INPUT = @"C:\Data\PersonalProjects\ExcelAutomationRunner\WorksheetToWorkbook1.xlsx";
        public const string WORKSHEET_TO_WORKBOOK_INPUT2 = @"C:\Data\PersonalProjects\ExcelAutomationRunner\WorksheetToWorkbook2.xlsx";
        internal const string OUTPUT_FILE = @"c:\temp\WorkbookTest.xlsx";
        internal const string TXT_INPUT_FILE = @"c:\temp\textTXT_to_XLSX.txt";
        private const string CSV_INPUT_FILE = @"C:\projects\OSDS\FAS\NoelLevitz.OSDS.DataDomain.Test\fafsa_output.csv";
        public const string QUOTED_CSV = @"C:\projects\OSDS\Fas\NoelLevitz.OSDS.DataDomain.Test\ImportFiles\bubba.txt";

        [TestMethod]
        public void GeneralFunction()
        {
            var result = new ExcelApplicationFunction().Work( untyped =>
            {
                var excel = untyped as Application;
                var workbook = excel.Workbooks.Open( @"C:\temp\bubba.xlsx" );
                var existingWorksheet = excel.Workbooks[1].Worksheets[1];
                existingWorksheet.UsedRange.Sort( excel.Columns[1], XlSortOrder.xlDescending );
                workbook.Save();
                workbook.Close();
            } );
            Assert.IsTrue( result.Item1 );
        }

        [TestMethod]
        public void SortOf()
        {
            var result = new Sorter().SortByColumn( 1, @"C:\temp\bubba.xlsx" );
            Assert.IsTrue( result.Item1 );
        }

        [TestMethod]
        public void XLSX_to_CSV()
        {
            var convertToXlsxResult = new CSV_to_XLSX_Converter().CSV_to_XLSX( CSV_INPUT_FILE );
            Assert.IsTrue( convertToXlsxResult.Item1, convertToXlsxResult.Item2 );

            /// Need to create new file so we don't overwrite test file ( after conversion we would end up back with CSV_INPUT_FILE )
            var newXLSXFileName = GetNewFilePathFrom( convertToXlsxResult.Item2, "temp" );
            File.Copy( convertToXlsxResult.Item2, newXLSXFileName );
            File.Delete( convertToXlsxResult.Item2 );

            Tuple<bool, string> result = new Tuple<bool, string>( false, string.Empty );
            try
            {
                result = new XLSX_to_CSV_Converter().XLSX_to_CSV( newXLSXFileName );
                Assert.IsTrue( result.Item1, result.Item2 );
                var contents = File.ReadAllLines( result.Item2 );
                Assert.AreEqual( "existingJunk1,existingJunk2,StudentId", contents[0] );
            }
            finally
            {
                if ( File.Exists( newXLSXFileName ) )
                {
                    File.Delete( newXLSXFileName );
                }
                if ( File.Exists( result.Item2 ) )
                {
                    File.Delete( result.Item2 );
                }
            }
        }

        [TestMethod]
        public void CSV_to_XLSX()
        {
            var result = new CSV_to_XLSX_Converter().CSV_to_XLSX( CSV_INPUT_FILE );
            Assert.IsTrue( result.Item1, result.Item2 );
            var newFilePath = Path.ChangeExtension( CSV_INPUT_FILE, ".xlsx" );
            CheckResults( newFilePath, null, null );
            File.Delete( newFilePath );

            /// Do a .txt file that contains csv
            File.Copy( QUOTED_CSV, TXT_INPUT_FILE );
            Assert.IsTrue( File.Exists( TXT_INPUT_FILE ) );
            result = new CSV_to_XLSX_Converter().CSV_to_XLSX( TXT_INPUT_FILE );
            Assert.IsTrue( result.Item1, result.Item2 );
            CheckResultsTxt( Path.ChangeExtension( TXT_INPUT_FILE, ".xlsx" ) );
            File.Delete( TXT_INPUT_FILE );
        }

        private static void CheckResultsTxt( string path )
        {
            using ( var p = new ExcelPackage( new FileInfo( path ) ) )
            {
                var worksheet = p.Workbook.Worksheets.FirstOrDefault();
                Assert.IsNotNull( worksheet );

                // row 1
                Assert.AreEqual( "this", worksheet.Cells[1, 1].Value );
                Assert.AreEqual( "that", worksheet.Cells[1, 2].Value );
                Assert.IsNull( worksheet.Cells[1, 3].Value );

                // row 2
                Assert.AreEqual( "like this", worksheet.Cells[2, 1].Value );
                Assert.AreEqual( "like that", worksheet.Cells[2, 2].Value );

                // row 3
                Assert.AreEqual( "a like", worksheet.Cells[3, 1].Value );
                Assert.AreEqual( "that unh", worksheet.Cells[3, 2].Value );
            }
        }

        private static void TearDown()
        {
            File.Delete( OUTPUT_FILE );
        }

        [TestMethod]
        public void WorksheetsToWorkbook()
        {
            var list = new List<string> { WORKSHEET_TO_WORKBOOK_INPUT, WORKSHEET_TO_WORKBOOK_INPUT2 };
            var result = new WorksheetsToWorkbook().ToWorkbook( list, OUTPUT_FILE, false );
            Assert.IsTrue( result.Item1, result.Item2 );
            CheckResults( OUTPUT_FILE, Path.GetFileName( WORKSHEET_TO_WORKBOOK_INPUT ).Replace( Path.GetExtension( OUTPUT_FILE ), string.Empty ), 2 );
            TearDown();
        }

        private static void CheckResults( string path, string sheetName, int? expectedWorksheetCount )
        {
            using ( var p = new ExcelPackage( new FileInfo( path ) ) )
            {
                if ( expectedWorksheetCount.HasValue )
                {
                    Assert.IsTrue( p.Workbook.Worksheets.Count == expectedWorksheetCount.Value );
                }

                ExcelWorksheet worksheet;

                if ( string.IsNullOrEmpty( sheetName ) )
                {
                    worksheet = p.Workbook.Worksheets.FirstOrDefault();
                }
                else
                {
                    worksheet = p.Workbook.Worksheets.FirstOrDefault( w => w.Name == sheetName );
                }
                Assert.IsNotNull( worksheet );

                // row 1
                Assert.AreEqual( "existingJunk1", worksheet.Cells[1, 1].Value );
                Assert.AreEqual( "existingJunk2", worksheet.Cells[1, 2].Value );
                Assert.AreEqual( "StudentId", worksheet.Cells[1, 3].Value );
                Assert.IsNull( worksheet.Cells[1, 5].Value );

                // row 2
                Assert.AreEqual( "blah1", worksheet.Cells[2, 1].Value );
                Assert.AreEqual( "blah2", worksheet.Cells[2, 2].Value );
                Assert.AreEqual( "abc", worksheet.Cells[2, 3].Value );
                Assert.IsNull( worksheet.Cells[2, 5].Value );

                // row 3
                Assert.AreEqual( "nuther1", worksheet.Cells[3, 1].Value );
                Assert.AreEqual( "nuther2", worksheet.Cells[3, 2].Value );
                Assert.AreEqual( "123", worksheet.Cells[3, 3].Value.ToString() );  // the FileFormatConverter made this into double.  Not sure what to do about that.
                Assert.IsNull( worksheet.Cells[3, 4].Value );
            }
        }

        private static string TryGetPath( string path, string fileNameWithoutExtension, string extension )
        {
            try
            {
                string newPath = Path.Combine( path, fileNameWithoutExtension + extension );
                Path.GetDirectoryName( newPath );  // this throws exception if too long.
                return newPath;
            }
            catch ( PathTooLongException )
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// Chops the file name down until it is either acceptable or can't be trimmed further (entire path too long).
        /// </summary>
        /// <returns>New path with trimmed file name (if necessary), extension intact</returns>
        public static string EnsureFileNameNotTooLong( string path )
        {
            lock ( _lock )
            {
                var fileNameWithoutExtension = Path.GetFileNameWithoutExtension( path );
                var extension = Path.GetExtension( path );
                var originalPath = path.Substring( 0, path.Length - fileNameWithoutExtension.Length - extension.Length );

                var length = Byte.MaxValue;
                fileNameWithoutExtension = fileNameWithoutExtension.Substring( 0, Math.Min( fileNameWithoutExtension.Length, length ) );
                var result = TryGetPath( originalPath, fileNameWithoutExtension, extension );

                while ( string.IsNullOrEmpty( result ) )
                {
                    length -= 10;
                    if ( length <= 0 )
                    {
                        throw new Exception( "Unable to make path short enough: " + path );
                    }
                    else
                    {
                        fileNameWithoutExtension = fileNameWithoutExtension.Substring( 0, Math.Min( fileNameWithoutExtension.Length, length ) );
                        result = TryGetPath( originalPath, fileNameWithoutExtension, extension );
                    }
                }

                return result;
            }
        }

        /// <summary>
        /// Like to make static, but this class pre-exists.
        /// </summary>
        /// <param name="dirty"></param>
        /// <returns>A file name that contains no invalid characters.</returns>
        public static string CleanFileName( string dirty )
        {
            return Path.GetInvalidFileNameChars().Aggregate( dirty, ( current, c ) => current.Replace( c.ToString(), string.Empty ) );
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="previousFilePath">A previously created / selected path</param>
        /// <param name="newFileName">A new (candidate since it may be cleaned) FILE NAME ONLY</param>
        /// <returns>A new path containing the same directory and extension as previousFilePath</returns>
        public static string GetNewFilePathFrom( string previousFilePath, string newFileName )
        {
            var directory = Path.GetDirectoryName( previousFilePath );
            var extension = Path.GetExtension( previousFilePath );
            newFileName = CleanFileName( newFileName ) + extension;
            /// Path.ChangeExtension is naive: if there are dots in the file name, it'll think the extension is anything to the right of the first dot.
            //return EnsureFileNameNotTooLong( Path.ChangeExtension( Path.Combine( directory, newFileName ), extension ) );
            var result = Path.Combine( directory, newFileName );
            return EnsureFileNameNotTooLong( result );
        }

    }
}
